

ALTER    function fn_get_process_flow_by_id (@process_step_id bigint)
returns  @fn_result Table(ProcessStep_ID bigint, 
			PS_Type varchar(20), 
			Process_Type_Name varchar(20))
As
begin
	declare @temp_process_step_id bigint
	declare @temp_ps_type varchar(20)
	declare @temp_ps_type_name varchar(20)
	declare @flag_step_id bigint
	declare @result Table(ProcessStep_ID bigint, 
			PS_Type varchar(20), 
			Process_Type_Name varchar(20))	

	select @temp_ps_type=PS_Type, @temp_ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @process_step_id
	insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@process_step_id, @temp_ps_type, @temp_ps_type_name)

	select @temp_process_step_id = ProcessStep_ID from Process_Step_Traverse_Table where Next_ProcessStep_ID = @process_step_id
	while @temp_process_step_id <> 0 and @temp_process_step_id is not null
	begin
		set @flag_step_id = 0
		select @temp_ps_type=PS_Type, @temp_ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @temp_process_step_id
		insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@temp_process_step_id, @temp_ps_type, @temp_ps_type_name)
		select @flag_step_id = ProcessStep_ID from Process_Step_Traverse_Table where Next_ProcessStep_ID = @temp_process_step_id
		set @temp_process_step_id = @flag_step_id
	end
	
	insert @fn_result select *from @result
	return
end 



